Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# AGGMAP

The AGGMAP command identifies an aggmap object as a specification for aggregation and adds an aggregation specification to the definition of the current aggmap object. In order to use AGGMAP to assign an aggregation specification to n aggmap object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

An alternative to the AGGMAP command is the EDIT AGGMAP statement, which is available only in OLAP Worksheet. The EDIT AGGMAP statement opens an Edit window in which you can add, delete, or change the aggregation specification for an aggmap object.

(Note that there are two other OLAP DML statements that are also sometimes referred to as "AGGMAP statements": AGGMAP ADD or REMOVE model statement that you can use to add or remove a model from an aggmap object of type AGGMAP, and AGGMAP SET that you can use to specify the default aggmap for a variable.)

Syntax

AGGMAP [specification]

Arguments

specification

  A multiline text expression that is the aggregation specification for the current aggmap object. Each statement is a line of the multiline text expression. When coding an aggmap statement at the command line level, separate statements with newline delimiters (`\n`), or use JOINLINES.

  An aggregation specification begins with an AGGMAP statement and ends with an `END` statement. Between these statements, you code one or more the following statements depending on the calculation that you want to specify. Minimally, an aggregation specification consists of a RELATION (for aggregation) statement. You can create more complex aggregation specifications by including one or more of the following statements in the specification:

    AGGINDEX
    BREAKOUT DIMENSION
    CACHE
    DIMENSION (for aggregation)
    DROP DIMENSION
    MEASUREDIM (for aggregation)
    MODEL (in an aggregation)
    PRECOMPUTE
    RELATION (for aggregation)

  Note: You cannot specify a conjoint dimension in the specification for the aggmap; use composites instead.

Notes

Creating Temporary or Custom Aggregates

Most aggmap objects are defined to calculate variable values that are dimensioned by permanent dimension members (that is, dimension members that persist from one session to another). However, users might wish to create their own aggregates at runtime for forecasting or what-if analysis, or just because they want to view the data in an unforeseen way. Adding temporary members to dimensions and aggregating data for those members is sometimes called creating temporary or custom aggregates. For example, you can use a MAINTAIN ADD SESSION statement like the one below to temporarily add a model to an aggmap object.

MAINTAIN dimension ADD SESSION member = model APPLY TO AGGMAP aggmap

Aggregating Variables Dimensioned by Compressed Composites

Keep the following points in mind when designing an aggregation specification for a variable dimensioned by a compressed composite:

- There is no support for partial aggregation. You cannot aggregate data up a composite using the skip-level approach. You must aggregate over the entire composite.

- RELATION (for aggregation) statements in the aggregation specification must be coded following the guidelines given in "RELATION Statements for Compressed Composites".

- There is no support for parallel aggregation. Instead, use multiple sessions to compute variables or partitions that have their own compressed composites.

- If possible, Oracle OLAP automatically performs incremental aggregation when you reaggregate a variable dimensioned by the compressed composite. In other words, Oracle OLAP determines what changes have occurred since the last aggregation, determines the smallest region of the variable that needs to be recomputed, and recomputes only that region.

  Consequently, there is no support for explicit incremental aggregation. You cannot aggregate a variable dimensioned by a compressed composite if the dimension status of the variable is limited. The status of the variable's dimensions must be ALLSTAT for the aggregation to succeed. You can, however, partition using a dense dimension with local compressed composites. In this way you can aggregate only those partitions that contain new data.

Aggregation Options and System Properties

A number of options can impact aggregation as outlined in Table A-5, "Aggregation Options".

Table A-1, "System Properties Used When Aggregating or Allocating Data" lists system properties that relate to aggregation or allocation.

Checking for Circularity

AGGREGATE automatically checks relations for circularity in and among multiple hierarchies. When you first define hierarchies, check for circularity by setting PRECOMPUTE statements to `NA` and AGGINDEX to `NO`. A XSHIERCK01 error during aggregation indicates that a circular hierarchy may have been detected. However, when the message includes a reference to UNDIRECTED, then multiple paths to an ancestor from a detail data cell have been detected. Some calculations require that a detail data cell use multiple paths to the same ancestor cell. When this is the case, then you need to set the MULTIPATHHIER option to `YES` before you execute the AGGREGATE command. Otherwise, you need to correct the error in the hierarchy structure. For more details about this error message and how to interpret it, see the MULTIPATHHIER option.

Examples

Example 6-21 Combining Pre-calculation and Calculation on the Fly

This example describes the steps you can take to pre-calculate some of the data in your analytic workspace and specify that the rest should be calculated when users request it.

Suppose you define an analytic workspace named `mydtb` that has a `units` variable with the following definition.

    DEFINE units INTEGER <time, SPARSE <product, geography>>

You now need to create and add a specification to the aggmap, which will specify the data that should be aggregated. This example shows you how to use an input file, which contains OLAP DML statements that define the aggmap and add a specification to it:

1. Identify the name of each dimension's hierarchy. When you have defined the hierarchies as self-relations, you use the names of the self-relations.

2. Decide which data to aggregate.

   Suppose you want to calculate data for all levels of the `time` and `product` dimensions, but not for `geography`. The `geography` dimension's lowest level of data is at the city level. The second level of the hierarchy has three dimension values that represent regions: `East`, `Central`, and `West`. The third level of the hierarchy has one dimension value: `Total`.

   Suppose that you want to pre-calculate the data for `East` and store it in the analytic workspace. You want the data for `Central`, `West`, and `Total` to be calculated only when users request that data — that data will not be stored in the analytic workspace. Therefore, you need to specify this information in the specification that you add to your aggmap object.

3. Create an ASCII text file named `units.txt`. Add the following OLAP DML statements to your text file.

       DEFINE units.agg AGGMAP <time, SPARSE <product, geography>>
       AGGMAP
       RELATION myti.parent
       RELATION mypr.parent
       RELATION myge.parent PRECOMPUTE ('East')
       END

   The preceding statements define an aggmap named `units.agg`, then add the three RELATION statements to the aggregation specification when you read the units.txt file into your analytic workspace.

4. To read the `units.txt` file into your analytic workspace, execute the following statement.

       INFILE 'inf/units.txt'

5. The `units.agg` aggmap should now exist in your analytic workspace. You can aggregate the `units` variable with the following statement.

       AGGREGATE units USING units.agg

   Now the data for `East` for all times and products has been calculated and stored in the analytic workspace.

6. Set up the analytic workspace so that when a user requests data for `Central`, `West`, or `Total`, that data will be calculated and displayed. It is generally a good idea to compile the aggmap object before using it with the AGGREGATE function, as shown by the following statement.

       COMPILE units.agg

   This is not an issue when you are just using the AGGREGATE command, because this statement compiles the aggmap object before it uses it. However, when you do not use the FUNCDATA keyword with the AGGREGATE command, the metadata that is needed to perform calculation on the fly has not been compiled yet. As long as you have performed all other necessary calculations (such as calculating models), it is a good practice to compile the aggmap when you load data. When you fail to do so, that means that every time a user opens the analytic workspace, that user will have to wait for the aggregation to be compiled automatically. In other words, when any data will be calculated on the fly, you can improve query performance for all of your users by compiling the aggmap before making the analytic workspace available to your users.

7. Add a property to the `units` variable.

       CONSIDER units
       PROPERTY '$NATRIGGER' 'AGGREGATE(units USING units.agg)'

   This property indicates that when a data cell contains an `NA` value, Oracle OLAP will call the AGGREGATE function to aggregate the data for that cell. Therefore, any `units` data that is requested by a user will be displayed. However, only the data for the `East` dimension value of the `geography` dimension has actually been aggregated and stored in the analytic workspace. All other data (for `Central`, `West`, and `Total`) is calculated only when users request it.

Example 6-22 Performing Non-additive Aggregation

This example shows how to use operators and arguments to combine additive and non-additive aggregation.

Suppose that you have defined four variables: `sales`, `debt`, `interest_rate`, and `inventory`. The variables have been defined with the same dimensionality where `cp` is a composite that has been defined with the `product` and `geography` dimensions.

    <time cp<product geography>>

Suppose you want to use one AGGREGATE command to aggregate all four variables. The `debt` variable requires additive aggregation. The `sales` variable requires a weighted sum aggregation, and `interest_rate` requires a hierarchical weighted average. Therefore, `sales` and `interest_rate` will each require a weight object, which you need to define and populate with weight values. `inventory` requires a result that represents the total inventory, which is the last value in the hierarchy.

You will specify the aggregation operation for `debt` and `inventory` with the OPERATOR keyword. However, because `sales` and `interest_rate` have aggregation operations that require weight objects, you must use the ARGS keyword to specify their operations. You define an operator variable to use the OPERATOR keyword. Typically, the operator variable is dimensioned by a measure dimension or a line item dimension.

Here are the steps to define the aggregation you want to occur:

1. Because you will also be using a measure dimension to define an argument variable to use with the ARGS keyword, define that `measure` dimension, as illustrated by the following statements.

       DEFINE measure DIMENSION TEXT
       MAINTAIN measure 'sales', 'debt', 'interest_rate', 'inventory'

   Note: Whenever you use a `measure` dimension in a RELATION statement, you must include a MEASUREDIM statement in the same aggregation specification

2. Define an operator variable named `opvar` and populate it.The statements specify that the aggregation for `debt` should use the `SUM` operator, and the aggregation for `inventory` should use the `HLAST` operator.

       DEFINE opvar TEXT <measure>
       opvar (measure 'sales') = 'WSUM'
       opvar (measure 'debt') = 'SUM'
       opvar (measure 'interest_rate') = 'HWAVERAGE'
       opvar (measure 'inventory') = 'HLAST'

3. Because `sales` and `interest_rate` require weight objects, define and populate those weight objects. The following statement defines a weight object named `currency` (to be used by `sales`).

       DEFINE currency DECIMAL <time geography>

   Notice that the `currency` variable is dimensioned only by `time` and `geography`. The purpose of this variable is to provide weights that act as currency conversion information for foreign countries; therefore, it is unnecessary to include the `product` dimension.

4. Populate `currency` with the weight values that you want to use.

5. The `interest_rate` variable's nonaddictive aggregation (hierarchical weighted average) requires the sum of the variable `debt`. In other words, `interest_rate` cannot be aggregated without the results of the aggregation of `debt`.

   You can now define an argument variable, which you will need to specify the aggregation results of `debt` as a weight object for `interest_rate`. You will use the same argument variable to specify `currency` as the weight object for the `sales` variable. The following statement defines an argument variable named `argvar`.

       DEFINE argvar TEXT <measure>

6. The next few statements populate the argument variable.

       argvar (measure 'sales') = 'weightby currency'
       argvar (measure 'debt') = NA
       argvar (measure 'interest_rate') = 'weightby debt'
       argvar (measure 'inventory') = NA

7. For the aggregation of `product` and `geography`, the data for the `sales`, `debt`, and `interest_rate` variables can simply be added. But the `inventory` variable requires a hierarchical weighted average. This means that it is necessary to define a second operator variable and a second argument variable, both of which will be used in the RELATION statement for `product` and `geography`.

   The following statements define the second operator variable and populate it.

       DEFINE opvar2 TEXT <measure>
       opvar (measure 'sales') = 'Sum'
       opvar (measure 'debt') = 'Sum'
       opvar (measure 'interest_rate') = 'Sum'
       opvar (measure 'inventory') = 'HWAverage'

   The following statements define the second argument variable and populate it.

       DEFINE argvar2 TEXT <measure>
       argvar (measure 'sales') = NA
       argvar (measure 'debt') = NA
       argvar (measure 'interest_rate') = NA
       argvar (measure 'inventory') = 'weightby debt'

8. Now create the aggmap, by issuing the following statements.

       DEFINE sales.agg AGGMAP <time, CP<product geography>>
       AGGMAP
       RELATION time.r OPERATOR opvar ARGS argvar
       RELATION product.r OPERATOR opvar2 ARGS argvar2
       RELATION geography.r OPERATOR opvar2 ARGS argvar2
       MEASUREDIM measure
       END

9. Finally, use the following statement to aggregate all four variables.

       AGGREGATE sales debt interest_rate inventory USING sales.agg

Example 6-23 Programmatically Defining an Aggmap

The following program uses the EXISTS function to test whether an AGGMAP already exists, and defines the AGGMAP when it does not. It then uses an AGGMAP statement to define the specification for the aggmap.

    DEFINE MAKEAGGMAP PROGRAM
    LD Create dynamic aggmap
    PROGRAM
    IF NOT EXISTS ('test.agg')
       THEN DEFINE test.agg AGGMAP <geography product channel time>
       ELSE CONSIDER test.agg
    AGGMAP JOINLINES(-
       'RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4)' -
       'RELATION product.parentrel' -
       'RELATION channel.parentrel' -
       'RELATION time.parentrel' -
       'END')
    END

Example 6-24 Creating an Aggmap Using an Input File

Suppose that you have created a disk file called `salesagg.txt`, which contains the following aggmap definition and specification.

    DEFINE sales.agg AGGMAP <time, product, geography>
    AGGMAP
    RELATION time.r PRECOMPUTE (time NE 'Year99')
    RELATION product.r PRECOMPUTE (product NE 'ALL')
    RELATION geography.r
    CACHE STORE
    END

To include the `sales.agg` aggmap in your analytic workspace, execute the following statement, where `inf` is the alias for the directory where the file is stored.

    INFILE 'inf/salesagg.txt'

The `sales.agg` aggmap has now been defined and contains the three RELATION statements and the CACHE statement. In this example, you are specifying that all of the data for the hierarchy for the `time` dimension, `time.r`, should be aggregated, except for any data that has a `time` dimension value of `Year99`. All of the data for the hierarchy for the `product` dimension, `product.r`, should be aggregated, except for any data that has a `product` dimension value of `All`. All `geography` dimension values are aggregated. The CACHE STORE statement specifies that any data that are rolled up on the fly should be calculated just once and stored in the cache for other access requests during the same session.

You can now use the `sales.agg` aggmap with an AGGREGATE command, such as.

    AGGREGATE sales USING sales.agg

In this example, any data value that dimensioned by a `Year99` value of the `time` dimension or an `All` value of the `product` dimension is calculated on the fly. All other data is aggregated and stored in the analytic workspace.

Example 6-25 Using Multiple Aggmaps

When you use a forecast, you must make sure that all of the input data that is required by that forecast has been pre-calculated. Otherwise, the forecast uses incorrect or nonexistent data. For example, suppose your forecast requires that all line items are aggregated. Using a `budget` variable that is dimensioned by `time`, `line`, and `division`, one approach would be to perform a complete aggregation of the `line` dimension, forecast the `dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR`, and then aggregate the remaining dimension, `division`.

You can support this processing by defining three aggmap objects:

1. Define the first aggmap, named `forecast.agg1`, which aggregates the data needed by the forecast. It contains the following statement.

       RELATION line.parentrel

2. Define the second aggmap, named `forecast.agg2`, which aggregates the data generated using the first aggmap and the forecast. It contains the following statement.

       RELATION division.parentrel PRECOMPUTE ('L3')

3. Define the third aggmap, named `forecast.agg3`, which contains the RELATION statements in the specifications of the first two aggmaps.

       RELATION line.parentrel
       RELATION division.parentrel PRECOMPUTE ('L3')

When your forecast is in a program named `fore.prg`, then you would use the following statements to aggregate the data.

    AGGREGATE budget USING forecast.agg1   "Aggregate over LINE
    CALL fore.prg                          "Forecast over TIME
    AGGREGATE budget USING forecast.agg2   "Aggregate over DIVISION
    "Compile the limit map for LINE and DIVISION
    COMPILE forecast.agg3
    "Use the combined aggmap for the AGGREGATE function
    CONSIDER budget
    PROPERTY 'NATRIGGER' 'AGGREGATE(budget USING forecast.agg3)'

Example 6-26 Using an AGGINDEX Statement in an Aggregation Specification

Suppose you have two variables, `sales1` and `sales2`, with the following definitions.

    DEFINE sales1 DECIMAL <time, SPARSE<product, channel, customer>>
    DEFINE sales2 DECIMAL <time, SPARSE<product, channel, customer>>

You do not want to precompute and commit all of the `sales` data to the database, because disk space is limited and you need to improve performance. Therefore, you need to create an aggmap, in which you specify which data should be pre-computed and which data should be calculated on the fly.

You define the aggmap, named `sales.agg`, with the following statement.

    DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, customer>>

Next, you use an AGGMAP statement to enter the following specification for `sales.agg`.

    RELATION time.r PRECOMPUTE (time NE 'Year99')
    RELATION product.r PRECOMPUTE (product NE 'All')
    RELATION channel.r
    RELATION customer.r
    AGGINDEX NO

This aggregation specification tells Oracle OLAP that all `sales` data should be rolled up committed to the database with the exception of any data that has a `time` dimension value of `Year99` or a `product` dimension value of `All`—the data for those cells is calculated the first time a user accesses them. The AGGINDEX value of `NO` tells Oracle OLAP not to create the indexes for data that should be calculated on the fly.

Now you execute the following statement.

    sales2 = AGGREGATE(sales1 USING sales.agg) ACROSS SPARSE -
       <product, channel, customer>

`sales2` now contains all of the data in `sales1`, plus any data that is aggregated for `Year99`—this is because `time` is not included in a composite.

On the other hand, the data that is aggregated for the `product` value of `All` is not computed and stored in `sales2`. This is because the `product` dimension is included in a composite—the indexes that are required for dimensions that are included in composites were not created because the aggregation specification contains an `AGGINDEX` `NO` statement. Since the indexes did not exist, Oracle OLAP never called the AGGREGATE function to compute the data to be calculated on the fly.

Example 6-27 Aggregating By Dimension Attributes

Assume that when your business makes a sales it keeps records of the customer's name, sex, age, and the amount of the sale. To hold this data, your analytic workspace contains a dimension named `customer` and three variables (named `customer_sex`, `customer_age`, and `sales`) that are dimensioned by `customer`.

    REPORT W 14 <customer_sex customer_age sales>

    CUSTOMER        CUSTOMER_SEX   CUSTOMER_AGE      SALES
    -------------- -------------- -------------- --------------
    Clarke         M                          26      26,000.00
    Smith          M                          47      15,000.00
    Ilsa           F                          24      33,000.00
    Rick           M                          33      22,000.00

You want to aggregate the detail sales data over sex and age to calculate the amount of sales you have made to males and females, and the amount of sales for different age ranges. To hold this data you will need an `INTEGER` variable that is dimensioned by hierarchical dimensions for sex and age. You will also need an aggmap object that specifies the calculations that Oracle OLAP will perform to populate this variable from the data in the `sales` variable.

To create and populate the necessary objects, you take the following steps:

1. Create and populate dimensions and self-relations for hierarchical dimensions named `sex` and `age`.

       DEFINE sex DIMENSION TEXT
       DEFINE sex.parentrel RELATION sex <sex>
       DEFINE age DIMENSION TEXT
       DEFINE age.parentrel RELATION age <age>

       AGE               AGE.PARENTREL
       -------------- --------------------
       0-20           All
       21-30          All
       31-50          All
       51-100         All
       No Response    All
       All            NA

       SEX               SEX.PARENTREL
       -------------- --------------------
       M              All
       F              All
       No Reponse     All
       All            NA

2. Create and populate relations that map the `age` and `sex` dimensions to the `customer` dimension.

       DEFINE customer.age.rel RELATION age <customer>
       DEFINE customer.sex.rel RELATION sex <customer>

       CUSTOMER         CUSTOMER.AGE.REL     CUSTOMER.SEX.REL
       -------------- -------------------- --------------------
       Clarke         21-30                M
       Smith          31-50                M
       Ilsa           21-30                F
       Rick           31-50                M

3. Create a variable named `sales_by_sex_age` to hold the aggregated data. Like the `sales` variable this variable is of type DECIMAL, but it is dimensioned by `sex` and `age` rather than by `customer`.

       DEFINE sales_by_sex_age VARIABLE DECIMAL <sex age>

4. Define an AGGMAP type aggmap object named `ssa_aggmap` to calculate the values of the `sales_by_sex_age` variable.

       DEFINE SSA_AGGMAP AGGMAP
       AGGMAP
       RELATION sex.parentrel OPERATOR SUM
       RELATION age.parentrel OPERATOR SUM
       BREAKOUT DIMENSION customer -
       BY customer.sex.rel, customer.age.rel OPERATOR SUM
       END

   Notice that the specification for the `ssa_aggmap` includes the following statements:

   - A BREAKOUT DIMENSION statement that specifies how to map the `customer` dimension of the `sales` variable to the lowest-level values of the `sales_by_sex_age` variable. This statement specifies the name of the dimension of the variable that contains the detail values (that is, `customer`) and the names of the relations (`customer.sex.rel` and `customer.age.rel`) that define the relations between `customer` dimension and the `sex` and `age` dimensions.

   - Two RELATION statements that specify how to aggregate up the `sex` and `age` dimensions of the `sales_by_sex_age` variable. Each of these statements includes the name of the child-parent relation (`sex.parentrel` or `age.parentrel`) that define the self-relation for the hierarchal dimension (`sex` or `age`).

5. Populate the `sales_by_sex_age` variable by issuing an AGGREGATE command that specifies that the detail data for the aggregation comes from the `sales` variable.

       AGGREGATE sales_by_sex_age USING ssa_aggmap FROM sales

   After performing the aggregation, a report of `sales_by_sex_age` shows the calculated values.

       ---------------------SALES_BY_SEX_AGE----------------------
                      ----------------------------SEX----------------------------
       AGE                  M              F          No Reponse        All
       -------------- -------------- -------------- -------------- --------------
       0-20                       NA             NA             NA             NA
       21-30               26,000.00      33,000.00             NA      59,000.00
       31-50               37,000.00             NA             NA      37,000.00
       51-100                     NA             NA             NA             NA
       No Response                NA             NA             NA             NA
       All                 63,000.00      33,000.00             NA      96,000.00

Example 6-28 Using a CACHE Statement in an Aggregation Specification

Suppose you have a `sales` variable with the following definition.

    DEFINE sales DECIMAL <time, SPARSE<product, channel, customer>>

You do not want to pre-compute and commit all of the `sales` data, because space is limited and you need to improve performance. Therefore, you need to create an aggmap, in which you will specify which data should be pre-computed and which data should be calculated on the fly.

You define the aggmap, named `sales.agg`, with the following statement.

    DEFINE sales.agg AGGMAP <time, SPARSE<product, channel, - customer>>

Next, you use the AGGMAP statement to enter the following aggregation specification for`sales.agg`.

    AGGMAP
    RELATION time.r PRECOMPUTE (time NE 'YEAR99')
    RELATION product.r PRECOMPUTE (product NE 'ALL')
    RELATION channel.r
    RELATION customer.r
    CACHE SESSION
    END

This aggregation specification tells Oracle OLAP that all `sales` data should be rolled up and committed, with the exception of any cells that have a time dimension value of `Year99` or a product dimension value of `ALL`; the data for those cells will be calculated the first time a user accesses them. Because the CACHE statement uses the SESSION keyword, that means that when those cells are calculated on the fly, the data is stored in the cache for the remainder of the Oracle OLAP session. That way, the next time a user accesses the same cell, the data will not have to be calculated again. Instead, the data will be retrieved from the session cache.

Example 6-29 Populating All Levels of a Hierarchy Except the Detail Level

Assume that your analytic workspace contains the relations and dimensions with the following definitions.

    DEFINE geog.d TEXT DIMENSION
    DEFINE geog.r RELATION geog.d <geog.d>
    DEFINE sales_by_units   INTEGER VARIABLE <geog.d>
    DEFINE sales_by_revenue DECIMAL VARIABLE <geog.d>
    DEFINE price_per_unit   DECIMAL VARIABLE <geog.d>

Assume that you create two aggmap objects. One aggmap object, named `units_aggmap`, is the specification to aggregate data in the `sales_by_units` variable. The other aggmap object, `revenue_aggmap`, is the specification to calculate all of the data except the detail data in the `sales_by_revenue` variable.

    DEFINE units_aggmap AGGMAP
    AGGMAP
      RELATION geog.r OPERATOR SUM
    END

    DEFINE revenue_aggmap AGGMAP
    AGGMAP
      RELATION geog.r OPERATOR WSUM ARGS WEIGHTBY price_per_unit
      CACHE NOLEAF
    END

The following steps outline the aggregation process:

1. Before either the `sales_by_unit` or `sales_by_revenue` variables are aggregated, they have the following values.

       GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
       --------- ------------- ----------------
       Boston                1               NA
       Medford               2               NA
       San Diego             3               NA
       Sunnydale             4               NA
       MA                    NA              NA
       CA                    NA              NA
       USA                   NA              NA

2. After the data for the `sales_by_unit` variable is aggregated, the `sales_by_unit` and `sales_by_revenue` variables have the following values.

       AGGREGATE sales_by_unit    USING units_aggmap

       GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
       --------- ------------- ----------------
       Boston                1               NA
       Medford               2               NA
       San Diego             3               NA
       Sunnydale             4               NA
       MA                    3               NA
       CA                    7               NA
       USA                  10               NA

3. After the data for the `sales_by_revue` variable is aggregated, the `sales_by_unit` and `sales_by_revenue` variables have the following values.

       AGGREGATE sales_by_revenue USING revenue_aggmap FROM units_aggmap

       GEOG.D    SALES_BY_UNIT SALES_BY_REVENUE
       --------- ------------- ----------------
       Boston                1               NA
       Medford               2               NA
       San Diego             3               NA
       Sunnydale             4               NA
       MA                    3             13.5
       CA                    7             31.5
       USA                  10             45.0

Example 6-30 Aggregating into a Different Variable

Assume that there is a variable named `sales` that is dimensioned by `time`, a hierarchical dimension, and `district`, a non-hierarchical dimension.

    DEFINE time DIMENSION TEXT
    DEFINE time.parentrel RELATION time <time>
    DEFINE district DIMENSION TEXT
    DEFINE sales VARIABLE DECIMAL <time district>

                 -----------------------SALES-----------------------
                 ---------------------DISTRICT----------------------
    TIME            North        South         West         East
    ------------ ------------ ------------ ------------ ------------
    1976Q1         168,776.81   362,367.87   219,667.47   149,815.65
    1976Q2         330,062.49   293,392.29   237,128.26   167,808.03
    1976Q3         304,953.04   354,240.51   170,892.80   298,737.70
    1976Q4         252,757.33   206,189.01   139,954.56   175,063.51
    1976                   NA           NA           NA           NA

Assume also that you want to calculate the total sales for each quarter and year for all districts except the `North` district. To perform this calculation using an aggmap object, you take the following steps:

1. Create a valueset named `not_north` that represents the values of `district` for which you want to aggregate data.

       DEFINE not_north VALUESET district
       LIMIT not_north TO ALL
       LIMIT not_north REMOVE 'North'

2. Define a variable named `total_sales_exclud_north` to hold the results of the calculation.

       DEFINE total_sales_exclud_north VARIABLE DECIMAL <time>

   Notice that, like `sales`, the `total_sales_exclud_north` variable is dimensioned by time. However, unlike `sales`, the `total_sales_exclud_north` variable is not dimensioned by `district` since it will hold detail data for each district, but only the total (aggregated) values for the `South`, `West`, and `East` districts (that is, all districts except `North`).

3. Define an aggmap object that specifies the calculation that you want performed.

       DEFINE agg_sales_exclud_north AGGMAP
       AGGMAP
       RELATION time.parentrel OPERATOR SUM
       DROP DIMENSION district OPERATOR SUM VALUES not_north
       END

   Notice that the aggregation specification consists of two statements that specify how to perform the aggregation:

   - A RELATION statement that specifies how to aggregate up the hierarchical `time` dimension

   - A DROP DIMENSION statement that specifies how to aggregate across the non-hierarchical `district` dimension. In this case, the DROP DIMENSION also uses the `not_north` valueset to specify that values for the `North` district are excluded when performing the aggregation

4. Aggregate the data.

       AGGREGATE total_sales_exclud_north USING agg_sales_exclud_north FROM sales

   The report of the `total_sales_exclud_north` variable shows the aggregated values.

       TIME             ALL_SALES_EXCEPT_NORTH
       ------------ ------------------------------
       1976Q1                           731,850.99
       1976Q2                           698,328.58
       1976Q3                           823,871.02
       1976Q4                           521,207.09
       1976                           2,775,257.69

Example 6-31 Using a MEASUREDIM Statement in an Aggregation Specification

Suppose you have defined a measure dimension named `measure`. You then define an operation variable named `myopvar`, which is dimensioned by `measure`. When you use `myopvar` in an aggregation specification, you must also include a MEASUREDIM statement that identifies `measure` as the dimension is included in the definition of `myopvar`.

The MEASUREDIM statement should follow the last RELATION (for aggregation) statement in the aggregation specification, as shown in the following example.

    DEFINE sales.agg AGGMAP <time, product, geography>
    AGGMAP
    RELATION time.r OPERATOR myopvar
    RELATION product.r
    RELATION geography.r
    MEASUREDIM measure
    END

Example 6-32 Solving a Model in an Aggregation

This example uses the `budget` variable.

    DEFINE budget VARIABLE DECIMAL <line time>
    LD Budgeted $ Financial

The `time` dimension has two hierarchies (`Standard` and `YTD`) and a parent relation named `time.parentrel` as follows.

    -----TIME.PARENTREL------
                   ----TIME.HIERARCHIES-----
    TIME             Standard       YTD
    -------------- ------------ ------------
    Last.YTD       NA           NA
    Current.YTD    NA           NA
    Jan01          Q1.01        Last.YTD
    ...
    Dec01          Q4.01        Last.YTD
    Jan02          Q1.02        Current.YTD
    Feb02          Q1.02        Current.YTD
    Mar02          Q1.02        Current.YTD
    Apr02          Q2.02        Current.YTD
    May02          Q2.02        Current.YTD
    Q1.01          2001         NA
    ...
    Q4.01          2001         NA
    Q1.02          2002         NA
    Q2.02          2002         NA
    2001           NA           NA
    2002           NA           NA

The relationships among line items are defined in the following model.

    DEFINE income.budget MODEL
    MODEL
    DIMENSION line time
    opr.income = gross.margin - marketing
    gross.margin = revenue - cogs
    revenue = LAG(revenue, 12, time) * 1.02
    cogs = LAG(cogs, 1, time) * 1.01
    marketing = LAG(opr.income, 1, time) * 0.20
    END

The following aggregation specification pre-aggregates all of the data. Notice that all of the data must be pre-aggregated because the model includes both LAG functions and a simultaneous equation.

    DEFINE budget.aggmap1 AGGMAP
    AGGMAP
    MODEL income.budget
    RELATION time.parentrel
    END

Example 6-33 Aggregating Up a Hierarchy

Suppose you define a `sales` variable with the following statement.

    DEFINE sales VARIABLE <time, SPARSE <product, geography>>

The aggregation specification for `sales` might include RELATION statements like the following.

    AGGMAP
    RELATION time.r PRECOMPUTE ('Yr98', 'Yr99')
    RELATION product.r
    RELATION geography.r PRECOMPUTE (geography NE 'Atlanta')
    END

The AGGREGATE command will aggregate values for `Yr98` and `Yr99`, over all of products, and over all geographic areas except for `Atlanta`. All other aggregates are calculated on the fly.

Example 6-34 Using Valuesets

Suppose you have a hierarchy dimension named time.type, whose dimension values are `Fiscal` and `Calendar`, in that order. These hierarchies are in conflict, and you want to precompute some `time` data but calculate the rest on the fly. Because the `Calendar` hierarchy is the last dimension value in the hierarchy dimension, this means that you need to define a valueset in order to get the correct results for the `Fiscal` hierarchy.

First, use the following statements to define and populate a valueset.

    DEFINE time.vs VALUESET time
    LIMIT time.vs TO 'Calendar' 'Fiscal'

You can then use the valueset in the following RELATION statement. Because the Fiscal hierarchy is the last hierarchy in the valueset, the data that is aggregated will be accurate for the Fiscal hierarchy.

    RELATION time.r(time.vs) PRECOMPUTE ('Yr99', 'Yr00')

Example 6-35 Aggregating with a RELATION Statement That Uses an ARGS Keyword

You can list the arguments in a RELATION statement directly in the statement or as the value of a text variable. For example, the following statement specifies `WEIGHTBY wobj` as an argument.

    RELATION time.r OPERATOR wsum ARGS WEIGHTBY wobj

Alternatively, you can define an variable for the argument whose value is the text of the WEIGHTBY clause.

    DEFINE argvar TEXT
    argvar = 'WEIGHTBY wobj'

Then the RELATION statement can specify the text variable that contains the WEIGHTBY clause.

    RELATION time.r OPERATOR WSUM ARGS argvar

Example 6-36 Aggregating Using a Measure Dimension

Suppose you want to use a single AGGREGATE command to aggregate the `sales`, `units`, `price`, and `inventory` variables. When you want to use the same operator for each variable, then you do not need to use a measure dimension. However, when you want to specify different aggregation operations, then you need to use a measure dimension.

The following statement defines a dimension named `measure`.

    DEFINE measure DIMENSION TEXT

You can then use a MAINTAIN statement to add dimension values to the `measure` dimension.

    MAINTAIN measure ADD 'sales', 'units', 'quota', 'inventory'

Use the `measure` dimension to dimension a text variable named `meas.opvar` that you will use as the operator variable.

    DEFINE meas.opvar TEXT WIDTH 2 <measure>

The following statements add values to OPVAR

    meas.opvar (measure 'sales') = 'SU'
    meas.opvar (measure 'units') = 'SU'
    meas.opvar (measure 'price') = 'HA'
    meas.opvar (measure 'inventory') = 'HL'

The aggregation specification might look like the following. Note that when you specify an operator variable in a RELATION statement, you must include a MEASUREDIM statement that specifies the name of the measure dimension (`measure` in the following example) in the aggregation specification.

    DEFINE opvar.aggmap AGGMAP
    AGGMAP
    RELATION geography.parentrel PRECOMPUTE (geography.lvldim 2 4)
    RELATION product.parentrel OPERATOR opvar
    RELATION channel.parentrel OPERATOR opvar
    RELATION time.parentrel OPERATOR opvar
    MEASUREDIM measure
    END

Example 6-37 Aggregating Using a Line Item Dimension

Suppose you have two variables, `actual` and `budget`, that have these dimensions.

    <time line division>

You want to use different methods to calculate different line items. You create a text variable that you will use as the operator variable.

    DEFINE line.opvar TEXT WIDTH 2 <line>

You then populate `line.opvar` with the appropriate operator for each line item, for example.

    line.opvar (line 'Net.Income') = 'SU'
    line.opvar (line 'Tax.Rate') = 'AV'

The aggregation specification might look like this.

    DEFINE LINE.AGGMAP AGGMAP
    AGGMAP
    RELATION time.parentrel OPERATOR line.opvar
    RELATION division.parentrel
    END

Example 6-38 Skip-Level Aggregation

Suppose you want to aggregate `sales` data. The `sales` variable is dimensioned by `geography`, `product`, `channel`, and `time`.

First, consider the hierarchy for each dimension. How many levels does each hierarchy have? What levels of data do users typically query? When you are designing a new workspace, what levels of data do your users plan to query?

Suppose you learn the information described in the following table about how users tend to query `sales` data for the `time` hierarchy.

Time Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often?
--- | --- | --- | ---
L1 | Year | `Year99`, `Year00` | yes
L2 | Quarter | `Q3.99`, `Q3.99`, `Q1.00` | yes
L3 | Month | `Jan99`, `Dec00` | yes

While the next table shows how your users tend to query `sales` data for the `geography` hierarchy.

Geography Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often?
--- | --- | --- | ---
L1 | World | `World` | yes
L2 | Continent | `Europe, Americas` | no
L3 | Country | `Hungary, Spain` | yes
L4 | City | `Budapest, Madrid` | yes

Finally, the next table shows how your users tend to query `sales` data for the `product` dimension hierarchy.

Product Level Names | Descriptive Level Name | Examples of Dimension Values | Do users query this level often?
--- | --- | --- | ---
L1 | All Products | `Totalprod` | yes
L2 | Division | `Audiodiv, Videodiv` | yes
L3 | Category | `TV, VCR` | yes
L4 | Product | `Tuner, CDplayer` | yes

Using this information about how users query data, you should use the following strategy for aggregation:

- Fully aggregate `time` and `product` because all levels are queried frequently.

- For the `geography` dimension, aggregate data for `L1` (`World`) and `L3` (`Country`) because they are queried frequently. However, `L2` is queried less often and so can be calculated on the fly.

The lowest level of data was loaded into the analytic workspace. The aggregate data is calculated from this source data.

Therefore, the aggregation specification might look like the following.

    RELATION time.parentrel
    RELATION geography.parentrel PRECOMPUTE (geog.leveldim 'L3' 'L1')
    RELATION product.parentrel

Example 6-39 Aggregation Specification with RELATION Statements That Include PRECOMPUTE Clauses

This aggregation specification uses PRECOMPUTE clauses in the RELATION statements to limit the data that is aggregated by the AGGREGATE command.

    DEFINE gpct.aggmap AGGMAP
    LD Aggmap for sales, units, quota, costs
    AGGMAP
    RELATION geography.parentrel PRECOMPUTE (geography.levelrel 'L3')
    RELATION product.parentrel PRECOMPUTE (LIMIT(product complement 'TotalProd'))
    RELATION channel.parentrel
    RELATION time.parentrel PRECOMPUTE (time NE '2001')
    END

## AGGINDEX

Within an aggregation specification, an AGGINDEX statement in an aggregation specification tells Oracle OLAP whether the compilation of that aggmap should create indexes (meaning, composite tuples) for data cells that are calculated on the fly by the AGGREGATE function. Therefore, the AGGINDEX statement has an effect on a dimension that is included in a composite but it has no effect on a dimension that is not included in a composite.

These indexes are used in the MODEL (in an aggregation) statement and in statements that use the ACROSS phrase to help Oracle OLAP loop over variables that are dimensioned by composites. These statements expect all data to be calculated. When you specify calculating some data on the fly, that data appears to be missing. When you set AGGINDEX to `YES`, then the statements try to access the missing data whether or not you are using the AGGREGATE function to perform calculation on the fly (meaning, you have added to the variable whose data is being aggregated an `NA` trigger property that calls the AGGREGATE function).

When the indexes have been created and you use AGGREGATION with the AGGREGATE function, then when MODEL (or a statement that uses the ACROSS phrase) requests the missing data, that data is calculated on the fly. That means that the results of the MODEL (or other statement) are correct, because the statement has all of the data that it needs.

When these indexes have not been created, the missing data cannot be calculated. As a result, the statements that need the indexes interpret the missing data as `NA` data, even when you use the AGGREGATE function.

Syntax

AGGINDEX {YES|NO}

Arguments

YES

  (Default) Tells the AGGMAP compiler to make sure that all possible indexes are created whenever an aggmap is recompiled. In other words, indexes are created both for the data that is being pre-calculated and the data that is calculated on the fly. This happens when you use a COMPILE statement to compile the aggmap, as well as when the AGGREGATE command automatically compiles an aggmap whose specification has changed since the last time it was compiled. The creation of all possible indexes results in a longer compilation time but faster execution of the AGGREGATE function. For a discussion of when AGGINDEX should be set to YES, see "When You Should Use an AGGINDEX Value of YES".

NO

  Does not create the indexes for data that is calculated on the fly. Omitting the creation of these index values accelerates the compilation time, but causes Oracle OLAP to treat the uncomputed data as `NA` data whenever the MODEL (in an aggregation) statement or the ACROSS phrase is used. For a discussion of when AGGINDEX should be set to NO, see "When You Should Use an AGGINDEX Value of NO".

Notes

When You Should Use an AGGINDEX Value of YES

The primary advantage to using an AGGINDEX value of `YES` is that then Oracle OLAP always try to access data that you have specified to be calculated on the fly. When you have created an $NATRIGGER property for a variable that calls the AGGREGATE function, the variable appears to have been fully precomputed. That means that when any `NA` value is encountered, the `NA` trigger is called during a MODEL (in an aggregation) statement or a statement using the ACROSS phrase. When the `NA` trigger is called, the AGGREGATE function is executed, and the data is calculated on the fly.

When AGGINDEX has a value of `NO`, then the `NA` trigger is called only to aggregate data for dimensions that are not included in a composite. Data for dimensions that are included in composites is interpreted as `NA` values.

For example, suppose you have two variables called `sales1` and `sales2`, which are defined with the following definitions.

    DEFINE sales1 DECIMAL <time, SPARSE <product, geography>>
    DEFINE sales2 DECIMAL <time, SPARSE <product, geography>>

Now suppose you have an aggmap object named `sales.agg`, which has the following definition.

    DEFINE sales.agg AGGMAP <time, SPARSE <product, geography>>

When you add a specification to the `sales.agg` aggmap, you enter RELATION (for aggregation) statements for `time`, `product` and `geography` with `PRECOMPUTE` clauses that specify `NA`. This specifies that no data is aggregated—instead, all of the data for any variable that uses this aggmap is calculated on the fly.

    RELATION time.r PRECOMPUTE (NA)
    RELATION product.r PRECOMPUTE (NA)
    RELATION geography.r PRECOMPUTE (NA)

Now attach the following $NATRIGGER property to the `sales1` variable.

    CONSIDER sales1
    PROPERTY '$NATRIGGER' 'AGGREGATE(sales1 USING sales.agg)'

Consider the effect of AGGINDEX in the following statement. Because you did not enter an AGGINDEX statement in the `sales.agg` aggregation specification, the default of AGGINDEX `YES` is assumed.

    sales2 = sales1 ACROSS SPARSE <product, geography>

This statement loops over the data in `sales1` and copies the values into `sales2`. This statement causes the `NA` trigger to call the AGGREGATE function for all of the data that you have specified to be calculated on the fly in `sales1`. This means that after the aggregation that `sales2` contains a copy of `sales1` plus all the aggregate data cells (the cells that would have been calculated if the `sales1` data had been completely precomputed, meaning, fully rolled up).

However, when you put an AGGINDEX `NO` statement in the `sales.agg` aggregation specification, then `sales2` contains a copy of the data in `sales1` and the aggregate data cells for the `time` dimension.

Note that in both cases, $NATRIGGER is called to aggregate `time` data, because the `time` dimension is not included in the composite, so the value of AGGINDEX has no effect on it.

When You Should Use an AGGINDEX Value of NO

You can use an AGGINDEX value of `NO` when you know that either of the following is true:

- Your application does not use a MODEL (in an aggregation) statement or an ACROSS phrase.

- The results of your MODEL (in an aggregation) statement or ACROSS phrase are additive, and data that needs to be aggregated can be calculated safely on the fly.

Each of the preceding cases ensures that the data that you have specified to be calculated on the fly is available at the appropriate time.

By setting AGGINDEX to `NO`, the size of the indexes is reduced, and overall application performance improves.

When Using an AGGINDEX Value Of NO Causes Problems

When you run a MODEL that assumes all data that should be aggregated has been aggregated, then you may get `NA` data where real data should occur. For instance, suppose you have a variable that has a composite that includes the `time` dimension. You perform a calculation that subtracts the fourth quarter from the total for the year. When the value of `Year` is to be calculated dynamically, and the AGGINDEX statement is set to `NO`, then the result of the calculation is `NA`. When the value of `Year` was precomputed or when AGGINDEX is set to `YES`, then the MODEL correctly calculates a result equal to the sum of the first three quarters.

Index Creation Is Based on Existing Data

Only the indexes that are needed to aggregate existing data are created when AGGINDEX has a value of `YES`. For example, suppose one of the dimensions in your composite is a dimension named `time`. The lowest-level data for the `time` dimension is at the monthly level. Therefore, the dimension values that are associated with the lowest-level data are `Jan99`, `Feb99`, and so on. The monthly data aggregates to quarters and to years. Suppose you have data for the first six months of the year. When AGGINDEX has a value of `YES`, indexes are created for the `Q1`, `Q2`, and `Yr99` dimension values, but not for `Q3` and `Q4`.

Reducing Compilation Time When AGGINDEX is YES

One disadvantage of using the default of AGGINDEX `YES` is that the compilation of the aggmap takes a longer time to complete. You can eliminate the cost of this extra time by using the `FUNCDATA` keyword with the AGGREGATE command. When you use the `FUNCDATA` keyword, all possible indexes (regardless of how you have limited your data) are created. However, do not use the `FUNCDATA` keyword when you use a different aggmap to execute the AGGREGATE command and the AGGREGATE function.

Examples

For an example of using an AGGINDEX statement, see Example 6-26, "Using an AGGINDEX Statement in an Aggregation Specification".

## BREAKOUT DIMENSION

Within an aggregation specification, a BREAKOUT DIMENSION statement specifies how a dimension of the target variable maps to one or more dimensions of the source variable. You use this statement in an aggregation specification when you will be aggregating the detail data from one variable (the source variable) into another variable (the target variable) that has a different dimension (that is, a "breakout" dimension) than the variable that contains the detail data.

Syntax

BREAKOUT DIMENSION dimname BY relationname [, relationname...] -

OPERATOR operation [ARGS argument]

where argument specifies the settings of various options and is one or more of the following phrases:

DIVIDEBYZERO {YES|NO}
DECIMALOVERFLOW {YES|NO}
NASKIP {YES|NO}
WEIGHTBY [WNAFILL {number | NA}] wobj

Arguments

dimname

  The name of a dimension in the variable that contains the detail data (that is, the source variable).

relationname

  The name of a relation whose values relate a dimension of the target variable to dimname.

OPERATOR

  Identifies the calculation method used to aggregate the data.

operation

  A keyword that describes the type of aggregation to perform. The keywords are listed in Table 6-1, "Aggregation Methods".

ARGS

  Indicates optional handling of the aggregation.

DIVIDEBYZERO

  Specifies whether to allow division by zero.

  YES allows division by zero; a statement involving division by zero executes without error but produces `NA` results.

  NO disallows division by zero; a statement involving division by zero stops executing and produces an error message.

  The default value is the current value of the DIVIDEBYZERO option.

DECIMALOVERFLOW

  Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numerical representation. Specify YES to allow overflow, which means that a calculation that generates overflow executes without error and produces `NA` results. Specify NO to disallow overflow, which means that; a calculation involving overflow stops executing and generates an error message. The default value is the current value of the DECIMALOVERFLOW option.

NASKIP

  Specifies whether `NA` values are input. Specify YES when you want Oracle OLAP to ignore `NA` values when aggregating which means that only actual values are used in calculations. Specify NO when you want Oracle OLAP to consider `NA` values are considered which means that when any of the values being considered are `NA`, the calculation returns `NA`.The default value is the current value of the NASKIP option.

  The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.

WEIGHTBY

  Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and can optionally include the WNAFILL keyword. For more information about the use of the WEIGHTBY phrase, see RELATION (for aggregation).

WNAFILL

  Indicates handling for `NA` values. The default values for WNAFILL vary depending on the value of operation.

number

  Substitutes a number for every `NA` value. That number will replace every `NA` value in the weight object, weight formula, or weight relation. The default for HWAVERAGE and SSUM is The default for HWFIRST, HWLAST, WAVERAGE, WFIRST, WLAST, and WSUM is `1.0` .

NA

  Specifies that `NA` values are to be specified as `NA`. `NA` is the default for OR.

  For more information about using the WNAFILL phrase, see RELATION (for aggregation).

wobj

  A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then `TRUE` has a weight of `1.0` and `FALSE` has a weight of `0.0`. A formula is queried only when needed, depending on the dimensionality of the formula and the of the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. For more information about specifying values for wobj, see RELATION (for aggregation).

Examples

For an example of using the BREAKOUT DIMENSION statement, see Example 6-27, "Aggregating By Dimension Attributes".

## CACHE

Within an aggregation specification, a CACHE statement tells Oracle OLAP whether to cache or store the calculated data, whether to populate leaf or detail data when the variable data is aggregated using detail data from another variable, and whether to cache `NA` values when a summary values calculates to `NA`.

Note: The CACHE statement is only one factor that determines whether variable data that has been aggregated on-the-fly using the AGGREGATE function is stored or cached. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".

Syntax

CACHE {NOSTORE|NONE|STORE|SESSION|DEFAULT} [LEAF|NOLEAF] [NA|NONA]

Arguments

NONE
NOSTORE

  For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP calculates the data each time the AGGREGATE function executes. When you specify either of these keywords, Oracle OLAP does not store or cache the data calculated by the AGGREGATE function.

STORE

  For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP stores data calculated by the AGGREGATE function in the variable in the database. When you specify this option, the results of the aggregation are permanently stored in the variable when the analytic workspace is updated and committed.

SESSION

  For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP caches data calculated by the AGGREGATE function in the session cache (see "What is an Oracle OLAP Session Cache?"). When you specify this option, the results of the aggregation are ignored during updates and commits and are discarded at the end of the session.

  Note: When SESSCACHE is set to `NO`, Oracle OLAP does not cache the data even when you specify `SESSION`. In this case, specifying `SESSION` is the same as specifying `NONE`.

DEFAULT

  (Default) For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP uses the value of the VARCACHE option to determine what to do with data that is calculated by the AGGREGATE function. See "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data".

LEAF

  When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP calculates the leaf data for the variable.

NOLEAF

  (Default) When the variable data is aggregated using detail data from another variable, specifies that Oracle OLAP does not calculate the leaf data for the variable.

NA

  For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP places any `NA` values that are the results of the execution of the AGGREGATE function in the Oracle OLAP session cache. In this case, when there is a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP does not recalculate the values for the variable. (For more information on the caching `NA` values, see "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER".)

NONA

  For data that is calculated using the AGGREGATE function, specifies that Oracle OLAP does not cache any `NA` values that are the results of the execution of the AGGREGATE function. In this case, when a variable has an $NATRIGGER property with an AGGREGATE function as its expression, Oracle OLAP recalculates the values for the variable.

Notes

When to Use NOSTORE

You should use NOSTORE when you know that your users are likely to modify pre-computed data, and you want any data that calculated by the AGGREGATE function to consistent with any of those users' changes.

In other words, suppose a user makes a change to detail-level data, such as `sales` figures for three stores, which are in a `geography` dimension. The `geography` dimension rolls up data from stores to cities to states to regions to countries. In other words, there are five levels in the `geography` dimension's hierarchy. Now suppose that users tend to access data only at the store level (your detail data), the regions level, and the countries level. Those are the levels for which you roll up sales data and commit it to the database. Because users do not access data at the city and state level, you specify that the data cells in those two levels will be calculated on the fly. When users modify the store-level data and then access city data, the city data will be calculated every time that a user requests it. Therefore, any changes that a user makes to the store-level details will accurately rollup to the city and state level every time that user accesses a data cell in the city or state level. (However, this will not be true of the data in the region and country levels, because those cells store pre-computed data.)

When to Use STORE or SESSION

The advantage to using STORE or SESSION is that it improves query performance. For example, suppose your users use a Table tool to look at a variable's data and an individual user requests the same data cells several times in the same session. When you use the default of NOSTORE, then any data that is not aggregated using the AGGREGATE command will have to be calculated every time the user requests that data even if you do not use the FORECALC keyword in the AGGREGATE function. On the other hand, when you use STORE or SESSION, then any given cell of data is calculated only once because it is available in either the variable or the cache for the entire session. Therefore, the next time a user requests that data cell, the data is returned from the variable or the cache instead of being calculated on the fly, which results in faster query time for the user.

Frequently you do not want the data that is calculated using the AGGREGATE function to be stored permanently in the database since that would defeat the purpose of calculating data on the fly.

- To ensure that the aggregated values cannot be permanently committed to the database, use SESSION.

- Use STORE when you know either of the following is true which also ensures that the data that is calculated on the fly using the AGGREGATE function will not be committed to the database:

  - The users of the analytic workspace can only open it as read-only

  - You know that the users of the analytic workspace will not or cannot issue UPDATE and COMMIT statements.

    Note: You should use STORE with caution when it is likely that your users modify pre-computed data, and they access data that you have specified to be calculated on the fly using the AGGREGATE function. The problem is that any data that is calculated using the AGGREGATE function before the user's modification will not reflect the user's change unless the user made the change using an AGGREGATE function with the FORCECALC keyword or unless there is a $AGGREGATE_FORCECALC property on the variable being aggregated

Examples

For examples of using a CACHE statement in an aggregation specification, see Example 6-28, "Using a CACHE Statement in an Aggregation Specification" and Example 6-29, "Populating All Levels of a Hierarchy Except the Detail Level".

## DIMENSION (for aggregation)

Within an aggregation specification, a DIMENSION statement sets the status to a single value of a dimension. When an aggregation specification does not specify such single values with DIMENSION statements, Oracle OLAP uses the current status values of the dimensions when performing the aggregation.

You use a DIMENSION statement to ensure that the status of a dimension is set to the value that you want it to have for the aggregation. You must use a separate DIMENSION statement for each dimension that is not shared by the source, basis, and target objects.

Syntax

DIMENSION dimension 'dimval '

Arguments

dimension

  the name of the dimension that you want to limit.

dimval

  A `TEXT` expression that is the single value of the dimension to which you want the status of the dimension set for the duration of an aggregation.

## DROP DIMENSION

Within an aggregation specification, a DROP DIMENSION statement specifies how non-hierarchical aggregation across variables is performed. You use this statement in aggregation specification when you will be aggregating the detail data from one variable (the source variable) into another variable (the target variable) and you want to aggregate across a non-hierarchical dimension of the source variable. In this case, the target variable has one less dimension (the "dropped" dimension) than the source variable because the values of the source variable associated with this dimension are aggregated to populate the target variable.

Syntax

DROP DIMENSION dimname [VALUES {valsetname|ALL} OPERATOR operation [ARGS argument]

where argument is one or more of the following phrases:

DIVIDEBYZERO {YES|NO}

DECIMALOVERFLOW {YES|NO}

NASKIP {YES|NO}

WEIGHTBY [WNAFILL {number|NA}] wobj

Arguments

dimname

  The name of a dimension in the source variable that contains the detail data.

VALUES

  Sets the status of dimname during the aggregation.

valsetname

  The name of a valueset object that determines the status of the dimension specified by dimname.

ALL

  Specifies that all of the values of dimname are in status.

OPERATOR

  Identifies the calculation method used to aggregate the data.

operation

  A keyword that describes the type of aggregation to perform. The keywords are listed in Table 6-1, "Aggregation Methods".

ARGS

  Indicates optional handling of the aggregation.

DIVIDEBYZERO

  Specifies whether to allow division by zero. Specify YES to allow division by zero which means that a statement involving division by zero executes without error but produces `NA` results. Specify NO to disallow division by zero which means that a statement involving division by zero stops executing and produces an error message. The default value is the current value of the DIVIDEBYZERO option.

DECIMALOVERFLOW

  Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numerical representation. Specify YES to allow overflow, which means that a calculation that generates overflow executes without error and produces `NA` results. Specify NO to disallow overflow which means that a calculation involving overflow stops executing and generates an error message. The default value is the current value of the DECIMALOVERFLOW option.

NASKIP

  Specifies whether `NA` values are input. Specify YES when you want Oracle OLAP to ignore `NA` values when aggregating which means that only actual values are used in calculations. Specify NO when you want Oracle OLAP to consider `NA` values when aggregating which means that when any of the values being considered are `NA`, the calculation returns `NA`. The default value is the current value of the NASKIP option.

  The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.

WEIGHTBY

  Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and can optionally include the WNAFILL keyword. For more information about the use of the WEIGHTBY phrase, see RELATION (for aggregation).

WNAFILL

  Indicates handling for `NA` values. The default values for WNAFILL vary depending on the value of operation. For more information about using the WNAFILL phrase, see RELATION (for aggregation).

number

  Substitutes a number for every `NA` value. That number will replace every `NA` value in the weight object, weight formula, or weight relation.

  - `0.0` is the default for HWAVERAGE and SSUM.

  - `1.0` is the default for HWFIRST, HWLAST, WAVERAGE, WFIRST, WLAST, and WSUM.

NA

  Specifies that `NA` values are to be specified as `NA`. `NA` is the default for OR.

wobj

  A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then `TRUE` has a weight of `1.0` and `FALSE` has a weight of `0.0`. A formula is queried only when needed, depending on the dimensionality of the formula and the of the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. For more information about specifying values for wobj, see RELATION (for aggregation).

Examples

For an example of using a DROP DIMENSION statement in an aggregation specification, see Example 6-30, "Aggregating into a Different Variable".

## MEASUREDIM (for aggregation)

Within an aggregation specification, a MEASUREDIM statement identifies the name of a measure dimension that is specified in the definition of an operator variable or an argument variable.

Syntax

MEASUREDIM name

Arguments

name

  The name of the measure dimension. A measure dimension is a dimension that you define. The dimension values are names of existing variables.

  Note: You cannot specify a measure dimension when it is included in the definition of the aggmap object.

Notes

Defining a Measure Dimension

The following statement defines a dimension named MEASURE.

    DEFINE measure DIMENSION TEXT

Populating a Measure Dimension

Once you have defined a measure dimension, you can then use a MAINTAIN statement to add dimension values to the MEASURE dimension.

The following statement adds the names of the `sales`, `units`, `price`, and `inventory` variables to `measure` as its dimension values.

    MAINTAIN measure ADD 'sales', 'units', 'price', 'inventory'

Using a Measure Dimension with an Operator Variable

The purpose of using measure dimensions is to take advantage of the flexibility of using non-additive aggregation operators. You can use measure dimensions in the definition of operation variables or argument variables.

The following statements show how to define an operator variable named `opvar` and populate it.

    DEFINE opvar TEXT <measure>
    opvar (measure 'sales') = 'SUM'
    opvar (measure 'inventory') = 'HLAST'

Examples

For an example of an aggregation specification that includes a MEASUREDIM statement, see Example 6-31, "Using a MEASUREDIM Statement in an Aggregation Specification".

## MODEL (in an aggregation)

Within an aggregation specification, a MODEL statement executes a predefined model.

Syntax

MODEL modelname [PRECOMPUTE ALL | PRECOMPUTE NA]

Arguments

modelname

  A text expression that contains the name of a predefined MODEL object.

PRECOMPUTE ALL
PRECOMPUTE NA

  Specifies whether the model is a static (precomputed) model or a dynamic model.

  - PRECOMPUTE ALL is the default and specifies a static model. The following conditions must be met:

    - Any RELATION (for aggregation) or MODEL statements that precede it in the aggregation specification must also be specified as PRECOMPUTE ALL.

    - Any RELATION (for aggregation) or MODEL statements that follow it in the aggregation specification can either be specified as PRECOMPUTE ALL or PRECOMPUTE NA.

  - PRECOMPUTE NA specifies a dynamic model. The following conditions must be met for runtime execution of the model:

    - All RELATION (for aggregation) statements in the aggregation specification must appear before the MODEL statements specified as PRECOMPUTE NA.

    - Any additional MODEL statements that follow it in the aggregation specification must also be specified as PRECOMPUTE NA.

Examples

For an example of using a model in an aggregation specification, see Example 6-32, "Solving a Model in an Aggregation".

## PRECOMPUTE

Within an aggregation specification, a PRECOMPUTE statement specifies which of the variable's aggregate values will be precomputed as a data maintenance procedure using the AGGREGATE command.

Note: An aggregation specification that has a PRECOMPUTE statement cannot have any PRECOMPUTE clauses in its RELATION (for aggregation) statements.

Syntax

PRECOMPUTE precompute-phrase

where precompute-phrase is one of the following:

  n% | AUTO
  ALL
  NA | NONE

Arguments

n%

  Specifies an explicit percentage of the aggregate variable values that will be aggregated as a database maintenance procedure using an AGGREGATE command. Oracle OLAP uses special functionality called the Aggregate Advisor to determine exactly which values are in the percentage.

AUTO

  Specifies that Oracle OLAP uses the Aggregate Advisor to determine how many and which aggregate variable values to aggregate as a database maintenance procedure using an AGGREGATE command.

ALL

  Specifies that all aggregated data will be precomputed using an AGGREGATE command.

NA
NONE

  Specifies that all values should be calculated on the fly using the AGGREGATE function (that is, that no data should be precalculated with the AGGREGATE command).

## RELATION (for aggregation)

Within an aggregation specification, a RELATION statement specifies how data is aggregated across a hierarchical dimension. Frequently, an aggregation specification contains one RELATION statement for each of the hierarchical dimensions of a variable.

Syntax

RELATION rel-name [(valueset...)] -

[PRECOMPUTE (precompute-phrase) -

[OPERATOR {operation|opvar}] -

[PARENTALIAS dimension-alias-name] -

[ARGS {argument|argsvar}] -

[LOAD_STATUS(status-valueset-name)]

where:

- precompute-phrase is one of the following:

    n% | AUTO
    dimension-values
    positions-of-dim-values
    level-relation-name level-name...
    valueset2
    ALL
    NA | NONE

  Note: An aggregation specification has PRECOMPUTE clauses in any of its RELATION statements cannot also have a PRECOMPUTE statement

- argument is one of the following:

    DIVIDEBYZERO {YES|NO}
    DECIMALOVERFLOW {YES|NO}
    NASKIP {YES|NO}
    WEIGHTBY [WNAFILL {number | NA}] wobj
    COUNT | NOCOUNT

- argsvar is a text variable that contains argument phrases for some or all dimension values.

Arguments

rel-name

  A relation that defines a hierarchy by identifying the parent of every dimension value in a hierarchy.

valueset

  Sets the status of one or more dimensions for the duration of the aggregation. It overrides the current status.

PRECOMPUTE

  Indicates that some dimension values are populated only with the AGGREGATE command. The PRECOMPUTE clause of the RELATION statement limits the data that is aggregated by the AGGREGATE command. In its simplest form, you can think of the PRECOMPUTE clause as working like a LIMIT dimension TO statement. Notice that the default limit is on the dimension, which is not explicitly named in the RELATION statement

  Note: You can.not specify a PRECOMPUTE phrase for a RELATION statement for a compressed composite.

n%

  Specifies an explicit percentage of the aggregate variable values that will be aggregated as a database maintenance procedure using an AGGREGATE command. Oracle OLAP uses special functionality called the Aggregate Advisor to determine exactly which values are in the percentage.

AUTO

  Specifies that Oracle OLAP uses the Aggregate Advisor to determine how many and which aggregate variable values to aggregate as a database maintenance procedure using an AGGREGATE command.

dimension-values

  A list of one or more values of dimension.

positions-of-dim-values

  For all dimensions except those with `INTEGER` or `NUMBER` values, the positions of the dimension values that you want precomputed. Specify the positions using `INTEGER` values, separated by commas.

valueset2

  The name of a valueset. When you include this argument, only data that is dimensioned by the dimension values in the valueset should be precalculated with the AGGREGATE command. The rest of the values can be calculated on the fly.

  Note that the current status of a dimension can also limit the data that is precalculated. See the AGGREGATE command for details.

ALL

  Specifies that data should be precalculated for all dimension values.

NA
NONE

  Specifies that all values should be calculated on the fly using the AGGREGATE function (that is, that no data should be precalculated with the AGGREGATE command).

level-relation-name level-name ...

  Specifies the levels of the dimension to be precomputed. For level-relation-name, specify, as a `TEXT` value, the name of the relation object that relates the values of the dimension to the names of the levels of the dimension. For level-name, specify, as `TEXT` values, the name of one or more levels using the same level names used in level-relation-name.

OPERATOR

  Identifies the calculation method used to aggregate the data.

operation

  A keyword that describes the type of calculation to perform. The keywords are listed in Table 6-1, "Aggregation Methods" and can be retrieved by issuing a AGGROPS statement. You can specify a fixed-length three-character abbreviation for the keywords by specifying only the first three characters.

  Table 6-1 Aggregation Methods

  Keyword | Description
  --- | ---
  AND | When any child data value is FALSE, then the data value of its parent is FALSE. A parent is TRUE only when all of its children are TRUE. (BOOLEAN variables only)
  AVERAGE | Adds data values, then divides the sum by the number of data values that were added together. When you use AVERAGE, there are special considerations described in "Average Operators".
  FIRST | The first non-`NA` data value.
  HAVERAGE | (Hierarchical Average) Adds data values, then divides the sum by the number of the children in the dimension hierarchy. Unlike AVERAGE, which counts only non-`NA` children, HAVERAGE counts all of the logical children of a parent, regardless of whether each child does or does not have a value. This keyword is not affected by the setting of the NASKIP option for argument.
  HFIRST | (Hierarchical First) The first data value that is specified by the hierarchy, even when that value is `NA`. This keyword is not affected by the setting of the NASKIP option for argument.
  HLAST | (Hierarchical Last) The last data value that is specified by the hierarchy, even when that value is `NA`. This keyword is not affected by the setting of the NASKIP option for argument.
  HWAVERAGE | (Hierarchical Weighted Average) Multiplies non-`NA` child data values by their corresponding weight values then divides the result by the sum of the weight values. Unlike WAVERAGE, HWAVERAGE includes weight values in the denominator sum even when the corresponding child values are `NA`. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument.
  HWFIRST | (Hierarchical Weighted First) The first data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is `NA`. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument.
  HWLAST | (Hierarchical Weighted Last) The last data value that is specified by the hierarchy multiplied by its corresponding weight value, even when that value is `NA`. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object. This keyword is not affected by the setting of the NASKIP option for argument.
  LAST | The last non-`NA` data value.
  MAX | The largest data value among the children of any parent data value.
  MIN | The smallest data value among the children of any parent data value.
  NOAGG | Do not aggregate any data for this dimension. Use this keyword only in an operator variable. It has no effect otherwise.
  OR | When any child data value is TRUE, then the data value of its parent is TRUE. A parent is FALSE only when all of its children are FALSE. (BOOLEAN variables only)
  SSUM | (Scaled Sum) Adds the value of a weight object to each data value, then adds the data values. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  SUM | (Default) Adds data values.
  WAVERAGE | (Weighted Average) Multiplies each data value by a weight factor, adds the data values, and then divides that result by the sum of the weight factors. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  WFIRST | (Weighted First) The first non-`NA` data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  WLAST | (Weighted Last) The last non-`NA` data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  WMAX | (Weighted Maximum) The largest data value among the children of any parent data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  WMIN | (Weighted Minimum) The smallest data value among the children of any parent data value multiplied by its corresponding weight value. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.
  WSUM | (Weighted Sum) Multiplies each data value by a weight factor, then adds the data values. When you use this keyword, you must include the WEIGHTBY argument keyword with a variable, formula, or relation as the weight object.

opvar

  A `TEXT` variable that you define that specifies a different the operation for each of its dimension values.

  Note: Not valid for variables dimensioned by compressed composites.

  The opvar argument is used in two ways:

  - Measure dimension -- Changes the aggregation method depending upon the variable being aggregated. This is useful when a single aggmap is used to aggregate several variables that need to be aggregated with different methods. Whether you pre-aggregate all of the measures in a single AGGREGATE command or in separate statements, AGGREGATE uses the operation variable to identify the calculation method. The values of the measure dimension are the names of the variables to be aggregated. It dimensions a text variable whose values identify the operation to be used to aggregate each measure. The aggregation specification must include a MEASUREDIM (for aggregation) statement that identifies the measure dimension. See Example 6-36, "Aggregating Using a Measure Dimension".

  - Line item dimension -- Changes the aggregation method depending upon the line item being aggregated. The line item dimension is typically non-hierarchical and identifies financial allocations. The line item dimension is used both to dimension the data variable and to dimension a text variable that identifies the operation to be used to aggregate each item. The operation variable is typically used to aggregate line items over time. You do not use the MEASUREDIM (for aggregation) statement in the aggmap. See Example 6-37, "Aggregating Using a Line Item Dimension".

  The opvar argument cannot be dimensioned by the dimension it is used to aggregate. For example, when you want to specify different operations for the `geography` dimension, then opvar cannot be dimensioned by `geography`.

  To minimize the amount of paging for the operator variable, define the operation variable as type of `TEXT` with a fixed width of `8`.

PARENTALIAS

  Specifies that an alias dimension for the dimension being aggregated is QDRd to the parent value currently being aggregated.

dimension-alias-name

  The name of the alias dimension for the dimension of rel-name.

ARGS

  Indicates optional handling of the aggregation.

DIVIDEBYZERO

  Specifies whether to allow division by zero.

  - YES allows division by zero; a statement involving division by zero executes without error but produces `NA` results.

  - NO disallows division by zero; a statement involving division by zero stops executing and produces an error message.

  The default value is the current value of the DIVIDEBYZERO option.

DECIMALOVERFLOW

  Specifies whether to allow decimal overflow, which occurs when the result of a calculation is very large and can no longer be represented by the exponent portion of the numerical representation.

  - YES allows overflow; a calculation that generates overflow executes without error and produces `NA` results.

  - NO disallows overflow; a calculation involving overflow stops executing and generates an error message.

  The default value is the current value of the DECIMALOVERFLOW option.

NASKIP

  Specifies whether `NA` values are input.

  - YES specifies that `NA` values are ignored when aggregating. Only actual values are used in calculations.

  - NO specifies that `NA` values are considered when aggregating. When any of the values being considered are `NA`, the calculation returns `NA`.

  The default value is the current value of the NASKIP option.

  The value that you specify for the NASKIP phrase does not effect calculation performed when you specify HAVERAGE, HFIRST, HLAST, HWAVERAGE, HWFIRST, HWLAST for operation.

WEIGHTBY

  Indicates that weighted aggregation is to be performed. You must include a WEIGHTBY clause when you specify HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, or WSUM for operation. The WEIGHTBY phrase always includes a wobj argument and, optionally, can include the WNAFILL keyword.

WNAFILL {number | NA}

  Indicates handling for `NA` values. The default values for WNAFILL vary depending on the value of operation. The default value for HWAVERAGE and SSUM is `0.0`. The default value for OR is `NA`. The default value for the other operators is `1.0`. WNAFILL defaults for each operator in an aggregation specification. In other words, when one RELATION statement includes a WSUM OPERATOR, then WNAFILL defaults to 1.0. When the next RELATION statement includes an SSUM OPERATOR, then WNAFILL defaults to `0.0`, and so on. See "Using WNAFILL".

wobj

  A variable, formula, or relation that provides the weighted values. It can be numeric or BOOLEAN. When wobj is BOOLEAN, then `TRUE` has a weight of `1.0` and `FALSE` has a weight of `0.0`. A formula is queried only when needed, depending on the dimensionality of the formula and the of the variable being aggregated. When wobj is a relation, it should be a one-dimensional self-relation. See Using Weighted Aggregation Methods for more information about specifying values for wobj.

COUNT

  Specifies that when Oracle OLAP aggregates a variable using this relation that it also populates the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".

NOCOUNT

  Specifies that when Oracle OLAP aggregates a variable using this relation that it does not populate the Aggcount variable associated with that variable. For more information on Aggcount variables, see "Aggcount Variables".

argsvar

  A `TEXT` variable that contains the argument options for some or all dimension values.

LOAD_STATUS

  Specifies that, for the aggregation, Oracle OLAP temporarily sets the status of the relation dimension using the values specified by status-valueset-name. Setting status in this way, limits the dimension to the specified values for the aggregation without changing the current status of the dimension

status-valueset-name

  A previously-defined valueset that is specifies the lowest-level values to have in status when performing the aggregation. When performing any aggregation using an aggmap with a RELATION statement with this clause, Oracle OLAP temporarily sets the status of the dimension to the values specified by status-valueset-name and their ancestors. The valueset specified by status-valueset-name must be a single dimensional valueset for the relation dimension (not the hierarchy dimension). Additionally, the valueset specified by status-valueset-name can not contain both a value and an ancestor of that value

Notes

RELATION Statements for Compressed Composites

When designing the aggregation specification, follow these guidelines when coding RELATION statements for compressed composites:

- Do not include a PRECOMPUTE clause.

- The HAVERAGE, HWAVERAGE, HWFIRST, HWLAST, SSUM, WAVERAGE, WFIRST, WLAST, WMAX, WMIN, and WSUM operators cause data values to change with each level of aggregation, regardless of sparsity. When possible, to insure the largest amount of overall compression, place RELATION statements with these operators at the beginning of your aggregation specification before RELATION statements that use an AND, AVERAGE, FIRST, HFIRST, HLAST, LAST, MAX, MIN, NOAGG, OR, or SUM operator.

- When an AGGMAP contains a RELATION statement that specifies an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE operator, any variable using that aggregation specification must be defined using a DEFINE VARIABLE statement with a WITH AGGCOUNT phrase.

- You can only specify a single aggregation operation. You cannot specify aggregation operations using an opvar variable.

Two Ways to use Valuesets

You can use valuesets to:

- Limit hierarchy dimensions. You can limit which hierarchies will be used by the AGGREGATE command and AGGREGATE function, as well as the order in which these hierarchies should be used. The valueset that you use specifies the names of a dimension's hierarchies. To use a valueset in this way, use the following syntax.

      RELATION rel-name (valueset)

  In this case, using valuesets provides a way to manage hierarchies that are in conflict with each other, meaning, when the same dimension value stores data for different children in different hierarchies (such as, `Q1` stores data for `Jan`, `Feb`, and `Mar` in the `Calendar` hierarchy, but `Q1` stores data for `May`, `Jun`, and `Jul` in the `Fiscal` hierarchy).

- Specify which values should be calculated on the fly by the AGGREGATE function and which values should be pre-calculated by the AGGREGATE command. The valueset that you use specifies the names of dimension values. To use a valueset in this way, use the following syntax.

      RELATION rel-name PRECOMPUTE (valueset)

  In this case, you use the valueset that follows the PRECOMPUTE keyword.

  When you use valuesets to limit hierarchy dimensions and when using more than one aggmap and the hierarchies are inconsistent, you must also use the FORCECALC keyword in the AGGREGATE function or have set a$AGGREGATE_FORCECALC property on the variable to be aggregated.

When You Change a PRECOMPUTE or an OPERATOR Clause

Any time you make changes to a PRECOMPUTE or an OPERATOR clause, you should aggregate the variable data again and recompile the aggmap in order to produce accurate data.

Aggregating Data Loaded into Different Hierarchy Levels

When data is loaded into dimension values that are at different levels of a hierarchy, then you need to be careful in how you set status in the PRECOMPUTE clause in a RELATION statement in your aggregation specification.

Suppose that a `time` dimension has a hierarchy with three levels: months aggregate into quarters, and quarters aggregate into years. Some data is loaded into month dimension values, while other data is loaded into quarter dimension values. For example, `Q1` is the parent of `January`, `February`, and `March`. Data for `March` is loaded into the `March` dimension value. But the sum of data for `January` and `February` is loaded directly into the `Q1` dimension value. In fact, the `January` and `February` dimension values contain `NA` values instead of data. Your goal is to add the data in `March` to the data in `Q1`.

When you attempt to aggregate `January`, `February`, and `March` into `Q1`, the data in `March` will simply replace the data in `Q1`. When this happens, `Q1` will only contain the `March` data instead of the sum of `January`, `February`, and `March`.

To aggregate data that is loaded into different levels of a hierarchy, create a valueset for only those dimension values that contain data.

    DEFINE all_but_q4 VALUESET time
    LIMIT all_but_q4 TO ALL
    LIMIT all_but_q4 REMOVE 'Q4'

Within the aggregation specification, use that valueset to specify that the detail-level data should be added to the data that already exists in its parent, `Q1`, as shown in the following statement.

    RELATION time.r PRECOMPUTE (all_but_q4)

Average Operators

There are a number of issues involved in using the AVERAGE, HAVERAGE, WAVERAGE, and HWAVERAGE operators:

- Oracle OLAP needs a separate `INTEGER` variable in which it stores the non-`NA` counts of the number of leaf nodes that contributed to aggregate values to calculate average values. When you want to aggregate a variable using one the average operators, include the WITH AGGCOUNT phrase in the DEFINE VARIABLE statement for the variable.

- Accuracy when averaging—All decimal data is converted to floating point format, both for storing and for calculations, consequently, in some cases, an average aggregation computed on a `DECIMAL` or `SHORTDECIMAL` variable can differ in the least significant digits from a result you compute by hand. For this reason, you might want to use the `NUMBER` data type when accuracy is more important than computational speed, such as variables that contain currency amounts. See "Numeric Expressions" for more information.

- Using Average operators when aggregating using an AGGREGATE command—When you use an average operator with the PRECOMPUTE keyword, the best practice is to use variables that have a decimal or `NUMBER` data type in order to ensure the accuracy of the results.

- Using Average operators for partial aggregations—When you use an average operator in a partial aggregation, then you must always aggregate using the same `INTEGER` variable (that is, Aggcount or Countvar variable). Do not change the values that are stored in this `INTEGER` variable between aggregations. Finally, the number of `INTEGER` variables must match the number of variables that are being aggregated.

HAVERAGE, HFIRST, HLAST, AND HWAVERAGE Operators

The "hierarchical" operators (HAVERAGE, HFIRST, HLAST, AND HWAVERAGE) are intended to provide an alternative form of `NA` handling.

FIRST, HFIRST, LAST, AND HLAST Operators

These operators rely on the existing order of the dimension values, which are assumed to be the default logical order of that dimension. For example, in a month dimension, it is assumed that February follows January, March follows February, and so on.

When you need to change the default order, use the MAINTAIN statement to do so. For example, suppose `Q1` includes `January`, `February`, and `March`, but you need to make `Februar`y the last month in the `Q1` instead of `March`. Use the following statement to do so.

    MAINTAIN time MOVE 'Feb01' AFTER 'Mar01'

Now, the LAST operator will assume that `FEB01` is the last month in `Q1`.

Read Permissions and Aggmaps

When you change the read permission to rel-name in a RELATION statement, then you must recompile the aggmap before using it with the AGGREGATE function. This is not an issue when you use the AGGREGATE command, because the aggmap will be recompiled automatically. However, when you do not have read access to every rel-name in the aggmap, then attempting to use that aggmap will result in an error message.

Using Weighted Aggregation Methods

When you use one of the weighted methods of aggregation, you must define and populate an object that contains the weights. You identify the aggregation method in the OPERATOR clause and the weight object in the ARGS clause.

The weight object can be a variable, a formula, or a relation. Special considerations apply depending on the type of object. the data type of the weight object, and whether or not you are performing a partial aggregation.

Weight Object Considerations Based on Type of Object The following considerations apply depending on the type of object that you use for the weight object:

- When the weight object is a variable, you can define it with a numeric or BOOLEAN data type. Use a variable as your weight object when you want to pre-calculate weight values and commit them to the database. You can use a variable weight object with any weight option.

- When the weight object is a relation, you should define it as a one-dimensional self-relation. You can use the weight object to specify that the weight for a specific cell is contained in the current variable at a different location. Use a relation as your weight object when you use a line item or a measure dimension. In this case, one line item is used as the weight to calculate the aggregate value of another line item. Using a relation enables you to specify another set of cells in the variable being aggregated as the weight values for a weighted operation.

- When the weight object is a formula, that formula will be queried only as often as needed, depending on the dimensionality of the formula and the dimensionality of the variable whose data is being aggregated. You can define the formula with a numeric or BOOLEAN data type. Use a formula as your weight object when you want to calculate weight values on the fly. A formula weight object is similar to a variable weight object, except that it cannot be aggregated. The value of a formula weight object is executed dynamically. Therefore, you cannot use a formula weight object with many of the weight options.

Considerations Based on Data Type of the Weight Object The following considerations apply when the weight object is numeric or `BOOLEAN`:

- When the weight object has a numeric data type, It is good practice for the weight object variable to have the same dimensionality (or a subset thereof) as the variable to which it corresponds, but it is not required. When you use Oracle numbers or decimals to define your data variable, then always use the same data type to define the corresponding weight object. Otherwise, use the same data type for the weight object and the data variable unless you use WAVERAGE or HWAVERAGE; in this case, use a decimal or `NUMBER` data type to define the weight object.

- When the weight object variable, formula, or relation that you define has a `BOOLEAN` data type, then `TRUE` represents a weight of `1.0` and `FALSE` represents a weight of `0.0`. Furthermore, when an `NA` value is multiplied by any value, the result is `NA`.

Weight Object Considerations When Performing Partial Aggregations When you use any operators that require the WEIGHTBY phrase, and you are performing a partial aggregation, then do not change the values that are stored in the weight object between AGGREGATE commands.

Using WNAFILL

For example, suppose you use the WSUM operator to perform currency conversion. The currency conversation rates will be applied at the detail data level. Only the detail data needs to be converted, because the variable data is aggregated after the conversion. In order to get the correct results, all of the non-detail level weight values in the weight object would have to be `1`. Although this strategy produces correct results, it is inefficient. The best practice is to use the default WNAFILL value of `1`. This specifies that all `NA` values in the weight object should be treated as if they have a weight of `1`. In this case, because the operator is WSUM, you do not have to include WNAFILL in the AGGREGATE command, because the default values are correct.

For example, the following statement causes the value `0.7` to be substituted for every `NA` value in the `salesw` weight object.

    AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL 0.7 salesw

When you do not want to specify a number to replace `NA` values, then you can use `NA` instead of a number, as shown in the following statement.

    AGGREGATE sales USING sales.agg WEIGHTBY WNAFILL NA salesw

Specifying `NA` after WNAFILL has the following effect:

- When the aggregation specification contains a WAVERAGE or a WSUM OPERATOR, then any child cell in the weight object that has an `NA` value is treated as an `NA` cell.

- When the aggregation specification contains an SSUM OPERATOR, then the results depend on how the Oracle OLAP option NASKIP is set. When NASKIP is set to `YES`, then any `NA` value is treated as 0.0. However, when NASKIP is set to `NO`, then any `NA` value is treated as an `NA` cell.

Effects of Dimension Status on Aggregation

A RELATION statement only aggregates those source data values that are in status—whether you set the status using LIMI T statements or a LOAD STATUS clause on the RELATION statement. The parent values are calculated regardless of whether they are in status or not. For example, when only `Jan01`, `Feb01`, and `Mar01` are in status for the `time` dimension, then `Q1.01` is calculated (but no other quarters), and `2001` is calculated (but no other years) using only `Q1.01` as input since the other quarters are NA. This can be useful when you want to aggregate just the new data in your analytic workspace.

Assume that there is a variable named `sales` that is dimensioned by `time`, a hierarchical dimension, and `district`, a non-hierarchical dimension.

    DEFINE time DIMENSION TEXT
    DEFINE time.parentrel RELATION time <time>
    DEFINE district DIMENSION TEXT
    DEFINE sales VARIABLE DECIMAL <time district>

    REPORT DOWN time sales

                 -----------------------SALES-----------------------
                 ---------------------DISTRICT----------------------
    TIME            North        South         West         East
    ------------ ------------ ------------ ------------ ------------
    1976Q1         168,776.81   362,367.87   219,667.47   149,815.65
    1976Q2         330,062.49   293,392.29   237,128.26   167,808.03
    1976Q3         304,953.04   354,240.51   170,892.80   298,737.70
    1976Q4         252,757.33   206,189.01   139,954.56   175,063.51
    1976                   NA           NA           NA           NA

Examples

For examples of aggregation specifications that include RELATION statements, see the examples in AGGMAP.